package com.lanvideo.dao;

import com.lanvideo.entity.Video;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;

/**
 * 视频数据访问层
 */
@Repository
public class VideoDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 保存视频信息
     */
    public int save(Video video) {
        // 确保status有默认值
        Integer status = video.getStatus() != null ? video.getStatus() : 0;
        
        String sql = "INSERT INTO video (title, intro, tags, path, uploader_id, upload_time, status) VALUES (?, ?, ?, ?, ?, ?, ?)";
        return jdbcTemplate.update(sql, video.getTitle(), video.getIntro(), 
                video.getTags(), video.getPath(), video.getUploaderId(), 
                video.getUploadTime(), status);
    }

    /**
     * 根据ID查找视频
     */
    public Video findById(Integer id) {
        String sql = "SELECT v.*, u.name as uploader_name FROM video v " +
                "LEFT JOIN user u ON v.uploader_id = u.id WHERE v.id = ?";
        List<Video> videos = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Video.class), id);
        return videos.isEmpty() ? null : videos.get(0);
    }

    /**
     * 获取所有视频（管理员使用）
     */
    public List<Video> findAll() {
        String sql = "SELECT v.*, u.name as uploader_name FROM video v " +
                "LEFT JOIN user u ON v.uploader_id = u.id ORDER BY v.upload_time DESC";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Video.class));
    }

    /**
     * 获取所有已审核通过的视频（学生可见）
     */
    public List<Video> findApprovedVideos() {
        String sql = "SELECT v.*, u.name as uploader_name FROM video v " +
                "LEFT JOIN user u ON v.uploader_id = u.id " +
                "WHERE v.status = 1 " +
                "ORDER BY v.upload_time DESC";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Video.class));
    }

    /**
     * 根据上传者ID获取视频列表
     */
    public List<Video> findByUploaderId(Integer uploaderId) {
        String sql = "SELECT v.*, u.name as uploader_name FROM video v " +
                "LEFT JOIN user u ON v.uploader_id = u.id WHERE v.uploader_id = ? " +
                "ORDER BY v.upload_time DESC";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Video.class), uploaderId);
    }

    /**
     * 搜索视频（管理员使用，搜索所有视频）
     */
    public List<Video> search(String keyword) {
        String sql = "SELECT v.*, u.name as uploader_name FROM video v " +
                "LEFT JOIN user u ON v.uploader_id = u.id " +
                "WHERE v.title LIKE ? OR v.intro LIKE ? OR v.tags LIKE ? OR u.name LIKE ? " +
                "ORDER BY v.upload_time DESC";
        String searchPattern = "%" + keyword + "%";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Video.class), 
                searchPattern, searchPattern, searchPattern, searchPattern);
    }

    /**
     * 高级搜索视频（管理员和老师使用）
     */
    public List<Video> advancedSearch(String title, String uploader, String tags, String intro, Integer status) {
        StringBuilder sql = new StringBuilder("SELECT v.*, u.name as uploader_name FROM video v " +
                "LEFT JOIN user u ON v.uploader_id = u.id WHERE 1=1");
        
        List<Object> params = new ArrayList<>();
        
        if (title != null && !title.trim().isEmpty()) {
            sql.append(" AND v.title LIKE ?");
            params.add("%" + title.trim() + "%");
        }
        
        if (uploader != null && !uploader.trim().isEmpty()) {
            sql.append(" AND u.name LIKE ?");
            params.add("%" + uploader.trim() + "%");
        }
        
        if (tags != null && !tags.trim().isEmpty()) {
            sql.append(" AND v.tags LIKE ?");
            params.add("%" + tags.trim() + "%");
        }
        
        if (intro != null && !intro.trim().isEmpty()) {
            sql.append(" AND v.intro LIKE ?");
            params.add("%" + intro.trim() + "%");
        }
        
        if (status != null) {
            sql.append(" AND v.status = ?");
            params.add(status);
        }
        
        sql.append(" ORDER BY v.upload_time DESC");
        
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(Video.class), params.toArray());
    }

    /**
     * 搜索已审核通过的视频（学生使用）
     */
    public List<Video> searchApprovedVideos(String keyword) {
        String sql = "SELECT v.*, u.name as uploader_name FROM video v " +
                "LEFT JOIN user u ON v.uploader_id = u.id " +
                "WHERE v.status = 1 AND (v.title LIKE ? OR v.intro LIKE ? OR v.tags LIKE ? OR u.name LIKE ?) " +
                "ORDER BY v.upload_time DESC";
        String searchPattern = "%" + keyword + "%";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Video.class), 
                searchPattern, searchPattern, searchPattern, searchPattern);
    }

    /**
     * 高级搜索已审核通过的视频（学生使用）
     */
    public List<Video> advancedSearchApprovedVideos(String title, String uploader, String tags, String intro) {
        StringBuilder sql = new StringBuilder("SELECT v.*, u.name as uploader_name FROM video v " +
                "LEFT JOIN user u ON v.uploader_id = u.id " +
                "WHERE v.status = 1");
        
        List<Object> params = new ArrayList<>();
        
        if (title != null && !title.trim().isEmpty()) {
            sql.append(" AND v.title LIKE ?");
            params.add("%" + title.trim() + "%");
        }
        
        if (uploader != null && !uploader.trim().isEmpty()) {
            sql.append(" AND u.name LIKE ?");
            params.add("%" + uploader.trim() + "%");
        }
        
        if (tags != null && !tags.trim().isEmpty()) {
            sql.append(" AND v.tags LIKE ?");
            params.add("%" + tags.trim() + "%");
        }
        
        if (intro != null && !intro.trim().isEmpty()) {
            sql.append(" AND v.intro LIKE ?");
            params.add("%" + intro.trim() + "%");
        }
        
        sql.append(" ORDER BY v.upload_time DESC");
        
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(Video.class), params.toArray());
    }

    /**
     * 删除视频
     */
    public int deleteById(Integer id) {
        String sql = "DELETE FROM video WHERE id = ?";
        return jdbcTemplate.update(sql, id);
    }

    /**
     * 更新视频信息
     */
    public int update(Video video) {
        String sql = "UPDATE video SET title = ?, intro = ?, tags = ? WHERE id = ?";
        return jdbcTemplate.update(sql, video.getTitle(), video.getIntro(), 
                video.getTags(), video.getId());
    }

    /**
     * 获取视频总数
     */
    public int getTotalCount() {
        String sql = "SELECT COUNT(*) FROM video";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
        return count != null ? count : 0;
    }

    /**
     * 获取待审核视频列表
     */
    public List<Video> findPendingVideos() {
        String sql = "SELECT v.*, u.name as uploader_name FROM video v " +
                "LEFT JOIN user u ON v.uploader_id = u.id " +
                "WHERE v.status = 0 " +
                "ORDER BY v.upload_time DESC";
        
        List<Video> videos = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Video.class));
        
        // 调试日志
        System.out.println("=== VideoDao.findPendingVideos Debug ===");
        System.out.println("SQL: " + sql);
        System.out.println("Found " + videos.size() + " pending videos");
        for (Video video : videos) {
            System.out.println("Video ID: " + video.getId() + 
                ", Title: " + video.getTitle() + 
                ", Status: " + video.getStatus() + 
                ", Uploader: " + video.getUploaderName());
        }
        System.out.println("=== End Debug ===");
        
        return videos;
    }

    /**
     * 审核通过视频
     */
    public int approveVideo(Integer videoId) {
        String sql = "UPDATE video SET status = 1 WHERE id = ?";
        return jdbcTemplate.update(sql, videoId);
    }

    /**
     * 审核拒绝视频
     */
    public int rejectVideo(Integer videoId, String reason) {
        String sql = "UPDATE video SET status = 2, reject_reason = ? WHERE id = ?";
        return jdbcTemplate.update(sql, reason, videoId);
    }

    /**
     * 增加视频观看次数
     */
    public int incrementViewCount(Integer videoId) {
        String sql = "UPDATE video SET view_count = view_count + 1 WHERE id = ?";
        return jdbcTemplate.update(sql, videoId);
    }

    /**
     * 获取视频观看次数
     */
    public Integer getViewCount(Integer videoId) {
        String sql = "SELECT view_count FROM video WHERE id = ?";
        return jdbcTemplate.queryForObject(sql, Integer.class, videoId);
    }
}
